﻿using System;
using System.Data;
using System.Data.SqlClient; 

namespace QualityApp
{
    /// <summary>
    /// SQLServer数据库访问层
    /// </summary>
    public class DataProvider
    {
        public static string ConnectString;
            //"Data Source=(local);Initial Catalog=SanJian.Quality;User ID=csframework;Password=test";
        /// <summary>
        /// 访问层实例
        /// </summary>
        private static DataProvider _Instance = null;

        /// <summary>
        ///  访问层实例
        /// </summary>
        public static DataProvider Instance
        {
            get
            {
                if (_Instance == null)
                {
                    _Instance = new DataProvider();
                }

                return _Instance;
            }
        }

        public SqlConnection CreateConnection()
        {

            SqlConnection conn = new SqlConnection(ConnectString);
            if (conn.State != ConnectionState.Connecting)
            {
                //ClearAllPools 重置（或清空）连接池。 如果调用时存在使用中的连接，则对这些连接进行相应的标记，并且在对这些连接调用 Close 时，放弃这些连接而不是将其返回连接池。 
                //SqlConnection.ClearAllPools();//处理偶发异常添加的------没有作用
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="sql">SQL 查询语句</param>
        /// <param name="tableName">数据表名</param>
        /// <returns></returns>
        public DataTable GetTable(string sql, string tableName)
        {
            SqlConnection conn = this.CreateConnection();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable(tableName);
            adp.Fill(dt);

            conn.Close();
            conn.Dispose();
            return dt;
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="command">SQL查询命令</param>
        /// <param name="tableName">数据表名</param>
        /// <returns></returns>
        public DataTable GetTable( SqlCommand command, string tableName)
        {
            SqlConnection connection = command.Connection;
            try
            {
                if (command.Connection == null) command.Connection = this.CreateConnection();
                DataTable table = new DataTable(tableName);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(table);
                this.CloseConnection(connection);
                return table;
            }
            catch (Exception ex)
            {
                if (connection.State != ConnectionState.Closed) connection.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 获取多个数据表(数据集）
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
            SqlConnection conn = this.CreateConnection();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();
            adp.Fill(ds);

            conn.Close();
            conn.Dispose();
            return ds;
        }

        /// <summary>
        /// 执行SQL命令，并返回查询结果的第一行的第一列。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            SqlConnection conn = this.CreateConnection();
            SqlCommand cmd = new SqlCommand(sql, conn);
            object o = cmd.ExecuteScalar();
            conn.Close();
            conn.Dispose();
            return o;
        }

        /// <summary>
        /// 执行SQL语句并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public int ExecuteNoQuery(string sql)
        {
            SqlConnection conn = this.CreateConnection();
            SqlCommand cmd = new SqlCommand(sql, conn);
            int ret = cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
            return ret;
        }


        /// <summary>
        /// 在事务内提交数据
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="SQL">SQL语句</param>
        /// <returns></returns>
        public int ExecuteNoQuery(SqlTransaction trans, string SQL)
        {
            SqlCommand cmd = new SqlCommand(SQL, trans.Connection, trans);
            int iValue = cmd.ExecuteNonQuery();
            return iValue;
        }


        /// <summary>
        /// 执行SQL语句并返回受影响的行数。
        /// </summary>
        /// <param name="command">SQL命令</param>
        /// <returns></returns>
        public int ExecuteNoQuery( SqlCommand command)
        {
            SqlConnection connection = command.Connection;
            try
            {
                if (command.Connection == null) command.Connection = this.CreateConnection();
                int ret = command.ExecuteNonQuery();
                this.CloseConnection(connection);
                return ret;
            }
            catch (System.Exception ex)
            {
                this.CloseConnection(connection);
                throw ex;
            }
        }

        /// <summary>
        /// 执行SQL命令返回第一行第一列的值
        /// </summary>
        /// <param name="command">SQL命令</param>
        /// <returns></returns>
        public object ExecuteScalar(SqlCommand command)
        {
            SqlConnection connection = command.Connection;
            try
            {
                if (command.Connection == null) command.Connection = this.CreateConnection();
                object ret = command.ExecuteScalar();
                this.CloseConnection(connection);
                return ret;
            }
            catch (Exception ex)
            {
                this.CloseConnection(connection);
                throw ex;
            }
        }

        /// <summary>
        /// 在事务内提交数据
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="SQL">SQL语句</param>
        /// <returns></returns>
        public object ExecuteScalar(SqlTransaction trans, string SQL)
        {
            SqlCommand cmd = new SqlCommand(SQL, trans.Connection, trans);
            object ret = cmd.ExecuteScalar();
            return ret;
        }

        /// <summary>
        /// 执行SQL语句并返回受影响的行数。
        /// </summary>
        /// <param name="command">SQL命令</param>
        /// <returns></returns>
        public int ExecuteSQL(SqlCommand command)
        {
            SqlConnection connection = command.Connection;
            try
            {
                if (command.Connection == null) command.Connection = this.CreateConnection();
                int iValue = command.ExecuteNonQuery();
                this.CloseConnection(connection);
                return iValue;
            }
            catch (Exception ex)
            {
                this.CloseConnection(connection);
                throw ex;
            }
        }
        /// <summary>
        /// 执行SQL语句并返回受影响的行数。
        /// </summary>
        public int ExecuteSQL(string SQL)
        {
            SqlConnection connection = this.CreateConnection();
            try
            {
                SqlCommand cmd = new SqlCommand(SQL, connection);
                int iValue = cmd.ExecuteNonQuery();
                this.CloseConnection(connection);
                return iValue;
            }
            catch (Exception ex)
            {
                this.CloseConnection(connection);
                throw ex;
            }
        }

        /// <summary>
        /// 在事务内提交数据
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="SQL">SQL语句</param>
        /// <returns></returns>
        public int ExecuteSQL(SqlTransaction trans, string SQL)
        {
            SqlCommand cmd = new SqlCommand(SQL, trans.Connection, trans);
            int iValue = cmd.ExecuteNonQuery();
            return iValue;
        }

        /// <summary>
        /// 获取多个数据表(数据集）
        /// </summary>
        public DataSet GetDataSet(SqlCommand command)
        {
            SqlConnection connection = command.Connection;
            try
            {
                if (command.Connection == null) command.Connection = this.CreateConnection();
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);
                this.CloseConnection(connection);
                return ds;
            }
            catch (Exception ex)
            {
                this.CloseConnection(connection);
                throw ex;
            }
        }

        /// <summary>
        /// 关闭SQL连接
        /// </summary>
        /// <param name="connection">当前连接</param>
        private void CloseConnection(SqlConnection connection)
        {
            if (connection == null) return;
            if (connection.State != ConnectionState.Closed) connection.Close();
        }
        //添加带返回值的存储过程调用
        public string ExecuteSpWithOutput( SqlCommand command)
        {
            SqlConnection con = command.Connection;
            try
            {
                if (command.Connection == null) command.Connection = this.CreateConnection();

                command.ExecuteScalar();
                this.CloseConnection(con);
                string strReturn = "";
                foreach (SqlParameter para in command.Parameters)
                {
                    if (para.Direction == ParameterDirection.ReturnValue)
                        strReturn = para.Value.ToString();
                }
                return strReturn;
            }
            catch (Exception ex)
            {
                this.CloseConnection(con);
                throw ex;
            }
        }
    } 
}
